/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package databeans;

import beans.*;
import com.sun.rowset.CachedRowSetImpl;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.sql.rowset.CachedRowSet;

import utils.CargarConfiguraciones;

/**
 *
 * @author Emerson
 */
public class MateriaDataBean {
private CachedRowSet rowSet;
private CargarConfiguraciones cc;

   

public MateriaDataBean() throws Exception{
    cc = new CargarConfiguraciones();
    Class.forName(cc.getValor("driver"));
    rowSet = new CachedRowSetImpl();
    rowSet.setUrl(cc.getValor("urlbase"));
    rowSet.setUsername(cc.getValor("usuario"));
    rowSet.setPassword(cc.getValor("clave"));
}

public ArrayList<MateriaBean> getListaMaterias()throws SQLException{
    ArrayList<MateriaBean> listaMaterias = new ArrayList<MateriaBean>();
String sql = "select " +
        "codmateria," +
        "nommateria," +
        "ciclo " +
        " from " +
        " materia order by ciclo";
rowSet.setCommand(sql);
rowSet.execute();
rowSet.beforeFirst();
while(rowSet.next()){
MateriaBean materia = new MateriaBean();
materia.setCodMateria(rowSet.getString(1));
materia.setNomMateria(rowSet.getString(2));
materia.setCiclo(rowSet.getString(3));
listaMaterias.add(materia);
}
    return listaMaterias;
}

public ArrayList<MateriaBean> getMateriasCicloActual()throws SQLException{
    ArrayList<MateriaBean> listaMaterias = new ArrayList<MateriaBean>();
    String ciclo = new CargarConfiguraciones().getValor("ciclo");
String sql = "select " +
        "codmateria," +
        "nommateria," +
        "ciclo " +
        " from " +
        " materia " +
        "where ciclo='" + ciclo + "'";
rowSet.setCommand(sql);
rowSet.execute();
rowSet.beforeFirst();
while(rowSet.next()){
MateriaBean materia = new MateriaBean();
materia.setCodMateria(rowSet.getString(1));
materia.setNomMateria(rowSet.getString(2));
materia.setCiclo(rowSet.getString(3));
listaMaterias.add(materia);
}
    return listaMaterias;
}

public ArrayList<MateriaBean> getListaMateriasByDocente(String codigo)throws SQLException{
    ArrayList<MateriaBean> listaMaterias = new ArrayList<MateriaBean>();
    String ciclo = new CargarConfiguraciones().getValor("ciclo");
    String sql = "select m.codmateria, "+
                 "m.nommateria, "+
                 "m.ciclo "+
                 "from materia m "+
                 "where m.ciclo = '"+ ciclo + "' "+
                 "and m.codmateria in (select dm.codmateria from docente_materia dm where dm.codigo ='"+codigo+"')";
    rowSet.setCommand(sql);
    rowSet.execute();
    rowSet.beforeFirst();
    while(rowSet.next()){
        MateriaBean materia = new MateriaBean();
        materia.setCodMateria(rowSet.getString(1));
        materia.setNomMateria(rowSet.getString(2));
        materia.setCiclo(rowSet.getString(3));
        listaMaterias.add(materia);
    }
    return listaMaterias;
}

public ArrayList<MateriaBean> getListaMateriasDistintasByDocente(String codigo)throws SQLException{
    ArrayList<MateriaBean> listaMaterias = new ArrayList<MateriaBean>();
    String ciclo = new CargarConfiguraciones().getValor("ciclo");
    String sql = "select m.codmateria, "+
                 "m.nommateria, "+
                 "m.ciclo "+
                 "from materia m "+
                 "where m.ciclo = '"+ ciclo + "' "+
                 "and m.codmateria not in (select dm.codmateria from docente_materia dm where dm.codigo ='"+codigo+"')";
    rowSet.setCommand(sql);
    rowSet.execute();
    rowSet.beforeFirst();
    while(rowSet.next()){
        MateriaBean materia = new MateriaBean();
        materia.setCodMateria(rowSet.getString(1));
        materia.setNomMateria(rowSet.getString(2));
        materia.setCiclo(rowSet.getString(3));
        listaMaterias.add(materia);
    }
    return listaMaterias;
}

public MateriaBean getMateria(String codmateria) throws SQLException{
    MateriaBean materia = new MateriaBean();
    String sql =  "select " +
            "codmateria," +
            "nommateria" +
            "ciclo " +
            " from " +
            " materia " +
            " where " +
            " codmateria = '" + codmateria + "'";
    rowSet.setCommand(sql);
    rowSet.execute();
    materia.setCodMateria(rowSet.getString(1));
    materia.setNomMateria(rowSet.getString(2));
    materia.setCiclo(rowSet.getString(3));
    return materia;
}

public ArrayList<MateriaBean> getMateriasbyCarnet  (String carnet)throws SQLException{
    
    ArrayList <MateriaBean> lista= new ArrayList <MateriaBean> ();
    String ciclo = new CargarConfiguraciones().getValor("ciclo");
        String sql = "select " +
                "m.codmateria, " +
                "m.nommateria, " +
                "m.ciclo " +
                "from materia m, " +
                "alumno_materia am " +
                "where " +
                "m.ciclo ='" + ciclo + "' and " +
                "m.codmateria=am.codmateria " +
                "and am.carnet='"+ carnet +"'";
        rowSet.setCommand(sql);
        rowSet.execute();
        rowSet.beforeFirst();
        while(rowSet.next()){
        MateriaBean materia = new MateriaBean();
        materia.setCodMateria(rowSet.getString(1));
        materia.setNomMateria(rowSet.getString(2));
        materia.setCiclo(rowSet.getString(3));
        lista.add(materia);
}
    
    
    return lista;
    
}
public ArrayList<MateriaBean> getMateriasDistintasbyCarnet  (String carnet)throws SQLException{
    
    ArrayList <MateriaBean> lista= new ArrayList <MateriaBean> ();
    String ciclo = new CargarConfiguraciones().getValor("ciclo");
        String sql = "select " +
                "m.codmateria, " +
                "m.nommateria, " +
                "m.ciclo " +
                "from materia m " +
                "where " +
                "m.ciclo ='" + ciclo + "' and " +
                "m.codmateria not in " +
                                "(select " +
                                "mm.codmateria " +
                                "from materia mm, " +
                                "alumno_materia am " +
                                "where mm.codmateria=am.codmateria " +
                                "and am.carnet='"+carnet+"')";
        rowSet.setCommand(sql);
        rowSet.execute();
        rowSet.beforeFirst();
        while(rowSet.next()){
        MateriaBean materia = new MateriaBean();
        materia.setCodMateria(rowSet.getString(1));
        materia.setNomMateria(rowSet.getString(2));
        materia.setCiclo(rowSet.getString(3));
        lista.add(materia);
}
    
    
    return lista;
    
}



 public void setMateria (MateriaBean materia) throws SQLException{
        String sql="INSERT INTO materia (" +
                        "codmateria, " +
                        "nommateria, " +
                        "ciclo)" +
                    "VALUES (" +
                        "'" + materia.getCodMateria() + "'," +
                        "'" + materia.getNomMateria() + "', " +
                        "'" + materia.getCiclo() + "')";
        rowSet.setCommand(sql);
        rowSet.execute();

    }

}
